﻿-- ============================================================
-- MembershipUser Reference Data Synchronization Script
-- ============================================================

CREATE TABLE #MembershipUser
(
	[UserName] [nchar](50) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[Pwd] [nvarchar](50) NULL,
	[AddTime] [datetime] NULL,
	[RoleId] [int] NOT NULL
);

SET NOCOUNT ON;

DECLARE
	@admin_role int, 
	@customer_role int,
	@guest_role int;

SELECT @admin_role = a.[Id] FROM [dbo].[MembershipRole] AS a WHERE a.[Name] = 'Administrators';
SELECT @customer_role = a.[Id] FROM [dbo].[MembershipRole] AS a WHERE a.[Name] = 'Customers';
SELECT @guest_role = a.[Id] FROM [dbo].[MembershipRole] AS a WHERE a.[Name] = 'Guests';


INSERT #MembershipUser ([UserName], [Pwd], [RoleId]) VALUES ( N'admin', N'', @admin_role)
INSERT #MembershipUser ([UserName], [Pwd], [RoleId]) VALUES ( N'customer', N'', @customer_role)
INSERT #MembershipUser ([UserName], [Pwd], [RoleId]) VALUES ( N'guest', N'', @guest_role)


MERGE [dbo].[MembershipUser] AS Target
	USING #MembershipUser AS Source ON (Target.[UserName] = Source.[UserName])
		WHEN MATCHED THEN 
			UPDATE SET Target.[UserName] = Source.[UserName]
					  ,Target.[RoleId] = Source.[RoleId]
		WHEN NOT MATCHED BY TARGET THEN
			INSERT ( [UserName], [Pwd], [RoleId])
			VALUES ( Source.[UserName], Source.[Pwd], Source.[RoleId]);

DROP TABLE #MembershipUser;


GO
